Recommendations for SQL Server Optimizations

Configure settings as follows to tune performance.

Operating System Settings

  • Set Power Mode (Control Panel) to "High Performance"
  • Set System Properties to "Adjust for Best Performance"
  • Configure PageFile.sys size should be 1.5 * Total Memory and located on SSD if possible
  • Add SQL User to Security for Perform Volume Maintenance Task

AntiVirus Software Exclusions

  • SQL Server Log Files: exclude the LOG folder for each SQL instance
  • SQL Server DB files: exclude *.MDF, *.LDF, *.NDF
  • SQL Trace Files: exclude *.TRC
  • Pagefile.sys
  • SQL Server Back-up Files (i.e., *.bak, *.trn)

Server level settings

NOTE: To access, right-click Server in SSMS > Properties.

  • Set min server memory to 8GB (8192 MB)
  • Set max server memory (based on total Operating System reported memory - 4GB
  • Set Cost Threshold to 75
  • Set Max Degree of Parallelism to 4
  • Set Backup Compression to On

Database Level Settings

Configure System Model database using the following settings:

  • Size Each Data File to 512 MB, 512 MB Growth
  • Size Log File to 64 MB, 256 MB Growth
  • Set Read Committed Snapshot Isolation to True
  • Set Recovery Mode to Simple

NOTE: It is important that this step be executed BEFORE the creation of the CranSoft database, and the execution of the DSP install file. And new DB that is created reads these default settings from the Model database, so it avoids incorrect DB settings.

TempDB Optimization

Configure the TempDB system database to these settings, preferably on SSD:

  • 8 Files - Each sized at 8GB (8192 MB), 512 MB Growth
  • Log File set to 512 MB, 512 MB Growth

Other SQL Server settings

NOTE: Consult with the Database Administrator for further details.

Go to Programs > SQL Server Configuration Manager > SQL Server Services > right click on SQL Server service > Properties > Startup Parameters > input “-T174” in Specify Startup Parameter > Apply (for each trace flag to add)

  • Add Trace Flag 174
  • Add Trace Flag 1117 and 1118 if version lower than SQL Server 2016
  • Set all User Databases to AUTOGROW_ALL_FILES (Only for SQL 2016 +)
  • Set up Weekly Index Maintenance Job
  • Set up Weekly Statistics Rebuild Job (or more often as required)
  • Set up Nightly Database Backup Job